﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using HotelSoft.HotelManagement_DTO;
using System.Collections;

namespace HotelSoft.HotelManagement_DAO
{
    public class KhachHangDAO : AbstractDAO
    {
        public DataTable laybangLuu_PHIEUDANGKY()
        {
            string sql = "select * from Luu_PHIEUDANGKY";
            return Config.AccessHelper.ExecuteQuery(sql);
        }
        public bool ThemKHACHHANG_PHIEUDANGKY(object AbstractDTO)
        {
            try
            {
                KhachHang_PhieuDangKyDTO dto = (KhachHang_PhieuDangKyDTO)AbstractDTO;
                string insertCommand = "INSERT INTO KHACHHANG_PHIEUDANGKY VALUES('" +
                   dto.CMND + "', '" +
                   dto.SoPDK + "')";
                Config.AccessHelper.ExecuteQuery(insertCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static DataTable Selectkhachhangtheocmn(object AbstractDTO)
        {
            KhachHangDTO dto = (KhachHangDTO)AbstractDTO;
            string sql = "SELECT * FROM KHACHHANG Where CMND='" + dto.CMND + "'";
            return Config.AccessHelper.ExecuteQuery(sql);
        }

        public DataTable SellectAll()
        {
            string sql = "select * from KHACHHANG";
            return Config.AccessHelper.ExecuteQuery(sql);
        }
        public ArrayList LayDanhSachKhachHang()
        {
            ArrayList arrDanhSach = new ArrayList();
            string searchCommand = "Select * from KHACHHANG";
            DataTable ds = Config.AccessHelper.ExecuteQuery(searchCommand);
            KhachHangDTO khachhang;

            int i = 0;
            int iSoDong = ds.Rows.Count;
            int iSoCot = ds.Columns.Count;
            while (i < iSoDong)
            {
                khachhang = new KhachHangDTO();
                khachhang.CMND = ds.Rows[i]["CMND"].ToString();
                khachhang.HOTENKH = ds.Rows[i]["HoTenKH"].ToString();
                khachhang.NGAYSINH = ds.Rows[i]["NgaySinh"].ToString();
                khachhang.GIOITINH = ds.Rows[i]["GioiTinh"].ToString();
                khachhang.DIACHI = ds.Rows[i]["DiaChi"].ToString();
                khachhang.QUOCTICH = ds.Rows[i]["QuocTich"].ToString();
                khachhang.SDTKH = ds.Rows[i]["SDTKH"].ToString();

                arrDanhSach.Add(khachhang);
                i++;
            }
            return arrDanhSach;
        }
        public override bool Them(object AbstractDTO)
        {
            try
            {
                KhachHangDTO dto = (KhachHangDTO)AbstractDTO;
                string insertCommand = "INSERT INTO KHACHHANG VALUES('" +
                   dto.CMND + "', N'" +
                   dto.HOTENKH + "', '" +
                   DateTime.Parse(dto.NGAYSINH).ToString("MM/dd/yyyy") + "', N'" +
                   dto.GIOITINH + "', N'" +
                   dto.DIACHI + "', N'" +
                   dto.QUOCTICH + "', '" +
                   dto.SDTKH + "')";
                Config.AccessHelper.ExecuteQuery(insertCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public override void Xoa(object AbstractDTO)
        {
            KhachHangDTO dto = (KhachHangDTO)AbstractDTO;
            string deleteCommand = "DELETE FROM KHACHHANG WHERE CMND = '" + dto.CMND + "'";
            Config.AccessHelper.ExecuteNonQuery(deleteCommand);
        }
        internal void Xoa(ArrayList dsKhachHang)
        {
            foreach (KhachHangDTO khachhang in dsKhachHang)
            {
                Xoa(khachhang);
            }
        }
        public override bool CapNhatThongTin(object AbstractDTO)
        {
            try
            {
                KhachHangDTO dto = (KhachHangDTO)AbstractDTO;
                string updateCommand = "UPDATE KHACHHANG SET HoTenKH = N'" + dto.HOTENKH + "', NgaySinh = '" +
                    DateTime.Parse(dto.NGAYSINH).ToString("MM/dd/yyyy") + "', GioiTinh = N'" + dto.GIOITINH + "', DiaChi = N'" + dto.DIACHI +
                    "', QuocTich = N'" + dto.QUOCTICH + "', SDTKH = '" + dto.SDTKH +
                    "' WHERE CMND = '" + dto.CMND + "'" ;
                Config.AccessHelper.ExecuteQuery(updateCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool SoSanhChuoi(String a, String b)
        {
            String a1 = a.ToLower();
            String b1 = b.ToLower();
            a1 = a1.Trim();
            b1 = b1.Trim();
            if (a1.Contains(b1))
                return true;
            return false;
        }
        public bool SoSanhKhachHang(KhachHangDTO dtoKhachHang1, KhachHangDTO dtoKhachHang2)
        {
            bool flag = false;

            if (dtoKhachHang2.CMND != null)
                if (dtoKhachHang1.CMND.ToUpper() == dtoKhachHang2.CMND.ToUpper())
                    flag = true;
                else
                    return false;
            if (dtoKhachHang2.HOTENKH != null)
                if (SoSanhChuoi(dtoKhachHang1.HOTENKH, dtoKhachHang2.HOTENKH) == true)
                    flag = true;
                else
                {
                    return false;
                }
            if (dtoKhachHang2.NGAYSINH != null)
                if (SoSanhChuoi(dtoKhachHang1.NGAYSINH, dtoKhachHang2.NGAYSINH) == true)
                    flag = true;
                else
                    return false;

            if (dtoKhachHang2.DIACHI != null)
                if (SoSanhChuoi(dtoKhachHang1.DIACHI, dtoKhachHang2.DIACHI) == true)
                    flag = true;
                else
                    return false;

            if (dtoKhachHang2.SDTKH != null)
                if (dtoKhachHang1.SDTKH.ToUpper() == dtoKhachHang2.SDTKH.ToUpper())
                    flag = true;
                else
                    return false;

            if (dtoKhachHang2.GIOITINH != null)
                if (SoSanhChuoi(dtoKhachHang1.GIOITINH, dtoKhachHang2.GIOITINH) == true)
                    flag = true;
                else
                    return false;

            if (dtoKhachHang2.QUOCTICH != null)
                if (SoSanhChuoi(dtoKhachHang1.QUOCTICH, dtoKhachHang2.QUOCTICH) == true)
                    flag = true;
                else
                    return false;
            return flag;
        }
        public ArrayList TraCuuKhachHang(ArrayList dsKhachHang, KhachHangDTO dtoNhanVienCanTraCuu)
        {
            ArrayList dsKetQuaTraCuu = new ArrayList();
            for (int i = 0; i < dsKhachHang.Count; i++)
            {
                if (SoSanhKhachHang(((KhachHangDTO)dsKhachHang[i]), dtoNhanVienCanTraCuu) == true)
                    dsKetQuaTraCuu.Add(((KhachHangDTO)dsKhachHang[i]));
            }
            return dsKetQuaTraCuu;
        }
        public override void TraCuu(object AbstractDTO)
        {
            throw new NotImplementedException();
        }

        public bool KTMA(object AbstractDTO)
        {
            KhachHangDTO dto = (KhachHangDTO)AbstractDTO;
            string strSQL;
            strSQL = "Select Count(*) From KHACHHANG where CMND='" + dto.CMND+ "'";
            return Config.AccessHelper.ExecuteScalar(strSQL);
        }

        public static int Timkhachhang(object AbstractDTO)
        {
            KhachHangDTO dto = (KhachHangDTO)AbstractDTO;
            string sql = "SELECT * FROM KHACHHANG WHERE CMND = '" + dto.CMND + "'";
            DataTable ds = Config.AccessHelper.ExecuteQuery(sql);
            int iSoDong = ds.Rows.Count;
            return iSoDong;
        }
    }
}
